package yhp.com.zijie.dao.impl;

import yhp.com.zijie.bean.Student;
import yhp.com.zijie.dao.StudentDao;
import yhp.com.zijie.util.DBUtils;
import yhp.com.zijie.util.StudentEnum;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class StudentDaoImpl extends DBUtils implements StudentDao {

    @Override
    public int addStudent(Student student) {

        int i = 0;
        try {
            String sql = "insert into Student values(null,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
            List params=new ArrayList();
            params.add(student.getStuName());
            params.add(student.getStuNo());
            params.add(student.getSex());
            params.add(student.getPhone());
            params.add(student.getEmail());
            params.add(student.getRegistered());
            params.add(student.getAddress());
            params.add(student.getProfession());
            params.add(student.getIdNumber());
            params.add(student.getPolitics());
            params.add(new Date());
            params.add(StudentEnum.READING.type);
            params.add(student.getIntroduction());
            params.add(student.getGid());

            i = update(sql, params);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }

        return i;
    }

    /**
     * 通过id查询学生信息
     */
    @Override
    public Student findById(int stuId) {

        Student student = new Student();
        try {
            String sql = "select * from student where stuid=?";
            List list = new ArrayList();
            list.add(stuId);
            resultSet = query(sql, list);

            while (resultSet.next()){


                student.setStuId(resultSet.getInt("stuid"));
                student.setStuNo(resultSet.getString("stuno"));
                student.setStuName(resultSet.getString("stuname"));
                student.setSex(resultSet.getInt("sex"));
                student.setPhone(resultSet.getString("phone"));
                student.setProfession(resultSet.getString("profession"));
                student.setAddress(resultSet.getString("address"));
                student.setRegDate(resultSet.getDate("regdate"));
                student.setEmail(resultSet.getString("email"));
                student.setIntroduction(resultSet.getString("introduction"));
                student.setGid(resultSet.getInt("gid"));
                student.setRegistered(resultSet.getString("registered"));
                student.setIdNumber(resultSet.getString("idnumber"));
                student.setPolitics(resultSet.getString("politics"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }

        return student;
    }

    @Override
    public int deleteById(int stuId) {
        int update = 0;
        try {
            String sql="update student  set state=? where stuid=?";
            List params=new ArrayList();
            params.add(StudentEnum.DELETE.type);//4,"删除"
            params.add(stuId);
            update = update(sql, params);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return update;

    }

    /**
     * 更新学生信息
     * @param student
     * @return
     */
    @Override
    public int updateStudent(Student student) {
        int i = 0;
        try {
            String sql = "update student set stuname=?, stuno=?,sex=?,phone=?,email=?,registered=?,address=?,profession=?,idnumber=?,politics=?,regdate=?,introduction=?,gid=? where stuid=?";
            List list = new ArrayList();
            list.add(student.getStuName());
            list.add(student.getStuNo());
            list.add(student.getSex());
            list.add(student.getPhone());
            list.add(student.getEmail());
            list.add(student.getRegistered());
            list.add(student.getAddress());
            list.add(student.getProfession());
            list.add(student.getIdNumber());
            list.add(student.getPolitics());
            list.add(new Date());
            list.add(student.getIntroduction());
            list.add(student.getGid());
            list.add(student.getStuId());

            i = update(sql, list);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }

        return i;
    }

    @Override
    public int total(String stuname, String stuno, int sex) {

        int total = 0;
        List paramList = new ArrayList();

        try {
            StringBuffer sql = new StringBuffer(" select count(*) from student where 1=1 and state!=4  ");

            if(stuname!=null&&stuname.length()>0){
                sql.append(" and stuname like ? ");
                paramList.add("%"+stuname+"%");
            }
            if(stuno!=null&&stuno.length()>0){
                sql.append(" and stuno=? ");
                paramList.add(stuno);
            }
            if(sex!=-1){
                sql.append(" and sex=? ");
                paramList.add(sex);
            }

            resultSet = query(sql.toString(), paramList);
            while (resultSet.next()){
                total=resultSet.getInt(1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }

        return total;
    }



    @Override
    public List<Student> getAllStudent(String stuname , String stuno , int sex , int pageIndex , int pageSize) {

        List<Student> studentList = new ArrayList<Student>();

        List paramList = new ArrayList();
        try {
            StringBuffer sql = new StringBuffer(" select * from student where 1=1 and state!=4 ");
            if(stuname!=null&&stuname.length()>0){
                sql.append(" and stuname like ? ");
                paramList.add("%"+stuname+"%");
            }
            if(stuno!=null&&stuno.length()>0){
                sql.append(" and stuno=? ");
                paramList.add(stuno);
            }
            if(sex!=-1){
                sql.append(" and sex=? ");
                paramList.add(sex);
            }

            //拼接分页的sql
            sql.append(" limit ?,?");
            //分页公式
            paramList.add((pageIndex-1)*pageSize);
            paramList.add(pageSize);

            resultSet = query(sql.toString(), paramList);

            while (resultSet.next()){
                Student student = new Student();
                student.setStuId(resultSet.getInt("stuid"));
                student.setStuName(resultSet.getString("stuname"));
                student.setStuNo(resultSet.getString("stuno"));
                student.setSex(resultSet.getInt("sex"));
                student.setPhone(resultSet.getString("phone"));
                student.setEmail(resultSet.getString("email"));
                student.setProfession(resultSet.getString("profession"));
                student.setRegistered(resultSet.getString("registered"));
                student.setAddress(resultSet.getString("address"));
                student.setPolitics(resultSet.getString("politics"));
                student.setRegDate(resultSet.getDate("regdate"));
                student.setState(resultSet.getInt("state"));
                student.setIntroduction(resultSet.getString("introduction"));
                student.setGid(resultSet.getInt("gid"));

                studentList.add(student);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            closeAll();
        }

        return studentList;
    }



}
